package com.artifact.dao.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.artifact.dao.RenewDao;
import com.artifact.model.Renew;

@Repository("renewDao")
public class RenewDaoImpl implements RenewDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public boolean addRenew(String userid, float renewMoney, String userInfo) {
		String sql = "INSERT INTO `yungou_assist`.`renew` (`userid`, `renewMoney`,`userInfo`) VALUES (?, ?, ?)";
		return jdbcTemplate.update(sql, userid, renewMoney, userInfo) > 0;
	}

	@Override
	public Renew getRenew(String userid) {
		String sql = "select * from renew where userid = ?";
		try {
			return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Renew>(Renew.class), userid);
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public boolean setRenewIsDeal(String userInfo) {
		String sql = "UPDATE `yungou_assist`.`renew` SET `isDeal`=1, `dealTime`=now() WHERE `userInfo`=?";
		return jdbcTemplate.update(sql, userInfo) > 0;
	}

	@Override
	public List<Renew> getRenewList(int firstPage, int pageSize, int sortType, boolean rankType) {
		String sql = "select * from renew order by renewTime desc limit ?,?";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Renew>(Renew.class), firstPage * pageSize, pageSize);
	}

	@Override
	public int getRenewListNum() {
		String sql = "select count(id) as id from renew";
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}

	@Override
	public int getRenewListNum(String managerType) {
		String sql = "select count(id) as id from renew WHERE `userInfo`=?";
		return jdbcTemplate.queryForObject(sql, Integer.class,managerType);
	}

	@Override
	public int getTwoRenewListNum() {
		String sql = "select count(id) as id from (select * from renew group by userid having(count(id)>1)) a";
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}

	@Override
	public List<Renew> getTwoRenewList(int firstPage, int pageSize, int sortType, boolean rankType) {
		String sql = "SELECT count(id)as id,userid,sum(renewMoney) as renewMoney,userInfo FROM yungou_assist.renew  group by userid having(count(id)>1) limit ?,?";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Renew>(Renew.class), firstPage * pageSize, pageSize);
	}

	@Override
	public int getRenewMoney(String managerType) {
		String sql = "select sum(renewMoney) as id from renew WHERE  `userInfo`=?";
		return jdbcTemplate.queryForObject(sql, Integer.class,managerType);
	}

	@Override
	public int getRenewMoney() {
		String sql = "select sum(renewMoney) as id from renew";
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}

	@Override
	public int getRenewUseUserMoney() {
		String sql = "select sum(renewMoney) as id from renew where userid in (SELECT userid FROM yungou_assist.user where user.userid in (select userid from yungou_assist.renew group by userid) and expirationTime > now())";
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}

	@Override
	public int getRenewNotPayMoney() {
		String sql = "select sum(renewMoney) as id from renew WHERE isDeal=0";
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}

	@Override
	public int getRenewNotPayMoney(String managerType) {
		String sql = "select sum(renewMoney) as id from renew WHERE `userInfo`=? and isDeal=0";
		return jdbcTemplate.queryForObject(sql, Integer.class,managerType);
	}

	@Override
	public int getRenewPayMoney(String managerType) {
		String sql = "select sum(renewMoney) as id from renew WHERE `userInfo`=? and isDeal=1";
		return jdbcTemplate.queryForObject(sql, Integer.class,managerType);
	}

	@Override
	public boolean deleteRenew(String id) {
		String sql = "DELETE FROM `yungou_assist`.`renew` WHERE `id`=?";
		return jdbcTemplate.update(sql, id) > 0;
	}

	@Override
	public List<Renew> getRenewByDayList() {
		String sql = "select DATE_FORMAT(renewTime,'%Y-%m-%d') as days,count(id) as id,DATE_FORMAT(renewTime,'%Y-%m-%d') as userid from renew group by days";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Renew>(Renew.class));
	}

	@Override
	public List<Renew> getRenewByUser() {
		String sql = "select * from renew GROUP BY userid";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Renew>(Renew.class));
	}

}
